﻿CREATE PROCEDURE [dbo].[proc_Orders_Department_Baidu]
	(
		@companyid int,
		@DepartmentId int,
		@MainProjectId int,
		@Stext nvarchar(50),
		@StartDate varchar(20),
		@EndDate varchar (20),
		@StartIndex int,
		@EndIndex int
	)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepartmentId

	;WITH list As(Select ROW_NUMBER() OVER (Order By DepId,CreateDate Desc) AS Row,
		Id,OId,CusName,
		(Select Top 1 d.DName From department d where d.id=a.DepId) As DepName,
		Salesman,
		IsNull(YingShouPrice,0) As YingShouPrice,
		IsNull(ShiShouPrice,0) As ShiShouPrice,
		CreateDate,StateId,
		IsNull(FinanceRenlingDate,'1900-01-01') As FinanceRenlingDate
		,ISNULL((SELECT Round(SUM(Project.CostPrice)*1.28,0) From Project WHERE Project.OrderId=a.Oid AND project.productid=23 AND project.FinanceAudit=2),0) AS Baidu
		From [Order] a
		Where CreateDate>=@sRq And CreateDate<=@eRq And
			OId In(Select Distinct OId From [Order_item]
			Where (@DepartmentId=0 Or DepId In(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T)) And 
			(@MainProjectId=0 Or ProductId=@MainProjectId)
		 )
		 And (@SText='' Or CusName Like '%'+@SText+'%' Or Salesman Like '%'+@SText+'%')
		 And Companyid=@companyid And StateId>0
	)
	Select *,(Select Count(0) From list) As RecordCount
	From list
	Where Row between @StartIndex and @EndIndex --AND baidu>0
End
